#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库验证和检查脚本
用于验证数据库表结构是否正确创建，并进行基本功能测试
"""

import mysql.connector
import logging
import sys
import json
from datetime import datetime, timedelta

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '962464',  # 请根据实际情况修改
    'database': 'news_aggregator',
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci'
}

def check_database_connection():
    """检查数据库连接"""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SELECT VERSION()")
        version = cursor.fetchone()[0]
        logger.info(f"✅ 数据库连接成功，MySQL版本: {version}")
        cursor.close()
        conn.close()
        return True
    except Exception as e:
        logger.error(f"❌ 数据库连接失败: {e}")
        return False

def check_tables_exist():
    """检查所有必要的表是否存在"""
    required_tables = [
        'users',
        'news_sources', 
        'news',
        'user_favorites',
        'search_history',
        'user_reading_behavior',
        'user_interest_tags',
        'news_tags',
        'user_similarity_cache',
        'recommendation_cache'
    ]
    
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        # 获取所有表
        cursor.execute("SHOW TABLES")
        existing_tables = [table[0] for table in cursor.fetchall()]
        
        logger.info(f"📋 数据库中现有表: {existing_tables}")
        
        missing_tables = []
        for table in required_tables:
            if table in existing_tables:
                logger.info(f"✅ 表 {table} 存在")
            else:
                logger.warning(f"❌ 表 {table} 不存在")
                missing_tables.append(table)
        
        cursor.close()
        conn.close()
        
        if missing_tables:
            logger.error(f"❌ 缺少以下表: {missing_tables}")
            return False
        else:
            logger.info("✅ 所有必要的表都存在")
            return True
            
    except Exception as e:
        logger.error(f"❌ 检查表存在性失败: {e}")
        return False

def check_table_structure():
    """检查表结构是否正确"""
    table_checks = {
        'users': ['id', 'username', 'email', 'password_hash', 'created_at'],
        'news': ['id', 'source_id', 'title', 'link', 'published_date', 'content', 'category'],
        'user_favorites': ['id', 'user_id', 'news_id', 'created_at'],
        'search_history': ['id', 'user_id', 'query', 'results_count', 'created_at']
    }
    
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        for table_name, required_columns in table_checks.items():
            logger.info(f"🔍 检查表 {table_name} 的结构...")
            
            cursor.execute(f"DESCRIBE {table_name}")
            columns = cursor.fetchall()
            existing_columns = [col['Field'] for col in columns]
            
            missing_columns = []
            for col in required_columns:
                if col in existing_columns:
                    logger.info(f"  ✅ 列 {col} 存在")
                else:
                    logger.warning(f"  ❌ 列 {col} 不存在")
                    missing_columns.append(col)
            
            if missing_columns:
                logger.error(f"❌ 表 {table_name} 缺少列: {missing_columns}")
            else:
                logger.info(f"✅ 表 {table_name} 结构正确")
        
        cursor.close()
        conn.close()
        return True
        
    except Exception as e:
        logger.error(f"❌ 检查表结构失败: {e}")
        return False

def check_foreign_keys():
    """检查外键约束是否正确"""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # 检查外键约束
        cursor.execute("""
            SELECT 
                TABLE_NAME,
                COLUMN_NAME,
                CONSTRAINT_NAME,
                REFERENCED_TABLE_NAME,
                REFERENCED_COLUMN_NAME
            FROM information_schema.KEY_COLUMN_USAGE 
            WHERE TABLE_SCHEMA = %s 
            AND REFERENCED_TABLE_NAME IS NOT NULL
        """, (DB_CONFIG['database'],))
        
        foreign_keys = cursor.fetchall()
        
        if foreign_keys:
            logger.info("✅ 外键约束检查:")
            for fk in foreign_keys:
                logger.info(f"  {fk['TABLE_NAME']}.{fk['COLUMN_NAME']} -> {fk['REFERENCED_TABLE_NAME']}.{fk['REFERENCED_COLUMN_NAME']}")
        else:
            logger.warning("⚠️  未检测到外键约束")
        
        cursor.close()
        conn.close()
        return True
        
    except Exception as e:
        logger.error(f"❌ 检查外键约束失败: {e}")
        return False

def check_indexes():
    """检查索引是否正确创建"""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        important_indexes = [
            ('users', 'username'),
            ('users', 'email'),
            ('news', 'source_id'),
            ('news', 'category'),
            ('user_favorites', 'user_id'),
            ('user_favorites', 'news_id'),
            ('search_history', 'user_id')
        ]
        
        logger.info("🔍 检查重要索引...")
        
        for table_name, column_name in important_indexes:
            cursor.execute(f"SHOW INDEX FROM {table_name} WHERE Column_name = %s", (column_name,))
            indexes = cursor.fetchall()
            
            if indexes:
                logger.info(f"  ✅ {table_name}.{column_name} 有索引")
            else:
                logger.warning(f"  ⚠️  {table_name}.{column_name} 没有索引")
        
        cursor.close()
        conn.close()
        return True
        
    except Exception as e:
        logger.error(f"❌ 检查索引失败: {e}")
        return False

def test_basic_operations():
    """测试基本数据操作"""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        logger.info("🧪 测试基本数据操作...")
        
        # 测试插入用户
        test_username = f"test_user_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        test_email = f"{test_username}@example.com"
        
        cursor.execute("""
            INSERT INTO users (username, email, password_hash) 
            VALUES (%s, %s, %s)
        """, (test_username, test_email, 'test_hash'))
        
        user_id = cursor.lastrowid
        logger.info(f"  ✅ 插入测试用户成功，ID: {user_id}")
        
        # 测试插入新闻源（如果不存在）
        cursor.execute("SELECT id FROM news_sources LIMIT 1")
        source = cursor.fetchone()
        
        if not source:
            cursor.execute("""
                INSERT INTO news_sources (name, category) 
                VALUES (%s, %s)
            """, ('测试新闻源', 'general'))
            source_id = cursor.lastrowid
            logger.info(f"  ✅ 插入测试新闻源成功，ID: {source_id}")
        else:
            source_id = source['id']
            logger.info(f"  ✅ 使用现有新闻源，ID: {source_id}")
        
        # 测试插入新闻
        cursor.execute("""
            INSERT INTO news (source_id, title, link, content, category) 
            VALUES (%s, %s, %s, %s, %s)
        """, (source_id, '测试新闻标题', 'http://example.com', '测试新闻内容', 'general'))
        
        news_id = cursor.lastrowid
        logger.info(f"  ✅ 插入测试新闻成功，ID: {news_id}")
        
        # 测试插入收藏
        cursor.execute("""
            INSERT INTO user_favorites (user_id, news_id) 
            VALUES (%s, %s)
        """, (user_id, news_id))
        
        logger.info("  ✅ 插入测试收藏成功")
        
        # 测试查询
        cursor.execute("""
            SELECT u.username, n.title, uf.created_at
            FROM user_favorites uf
            JOIN users u ON uf.user_id = u.id
            JOIN news n ON uf.news_id = n.id
            WHERE u.id = %s
        """, (user_id,))
        
        result = cursor.fetchone()
        if result:
            logger.info(f"  ✅ 联表查询成功: {result['username']} 收藏了 {result['title']}")
        
        # 清理测试数据
        cursor.execute("DELETE FROM user_favorites WHERE user_id = %s", (user_id,))
        cursor.execute("DELETE FROM news WHERE id = %s", (news_id,))
        cursor.execute("DELETE FROM users WHERE id = %s", (user_id,))
        
        logger.info("  ✅ 清理测试数据完成")
        
        conn.commit()
        cursor.close()
        conn.close()
        
        logger.info("✅ 基本数据操作测试通过")
        return True
        
    except Exception as e:
        logger.error(f"❌ 基本数据操作测试失败: {e}")
        return False

def generate_report():
    """生成数据库状态报告"""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        report = {
            'database_name': DB_CONFIG['database'],
            'check_time': datetime.now().isoformat(),
            'tables': {},
            'summary': {}
        }
        
        # 获取表信息
        cursor.execute("""
            SELECT 
                TABLE_NAME,
                TABLE_COMMENT,
                TABLE_ROWS,
                ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as SIZE_MB
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = %s 
            ORDER BY TABLE_NAME
        """, (DB_CONFIG['database'],))
        
        tables = cursor.fetchall()
        total_size = 0
        total_rows = 0
        
        for table in tables:
            table_name = table['TABLE_NAME']
            report['tables'][table_name] = {
                'comment': table['TABLE_COMMENT'],
                'rows': table['TABLE_ROWS'],
                'size_mb': table['SIZE_MB']
            }
            total_size += table['SIZE_MB'] or 0
            total_rows += table['TABLE_ROWS'] or 0
        
        report['summary'] = {
            'total_tables': len(tables),
            'total_rows': total_rows,
            'total_size_mb': round(total_size, 2)
        }
        
        cursor.close()
        conn.close()
        
        # 保存报告
        with open('database_check_report.json', 'w', encoding='utf-8') as f:
            json.dump(report, f, ensure_ascii=False, indent=2)
        
        logger.info("📊 数据库状态报告已生成: database_check_report.json")
        return report
        
    except Exception as e:
        logger.error(f"❌ 生成报告失败: {e}")
        return None

def main():
    """主函数"""
    logger.info("🚀 开始数据库验证检查...")
    
    checks = [
        ("数据库连接", check_database_connection),
        ("表存在性", check_tables_exist),
        ("表结构", check_table_structure),
        ("外键约束", check_foreign_keys),
        ("索引", check_indexes),
        ("基本操作", test_basic_operations)
    ]
    
    results = {}
    all_passed = True
    
    for check_name, check_func in checks:
        logger.info(f"\n{'='*50}")
        logger.info(f"🔍 执行检查: {check_name}")
        logger.info('='*50)
        
        try:
            result = check_func()
            results[check_name] = result
            if not result:
                all_passed = False
        except Exception as e:
            logger.error(f"❌ 检查 {check_name} 时发生异常: {e}")
            results[check_name] = False
            all_passed = False
    
    # 生成报告
    logger.info(f"\n{'='*50}")
    logger.info("📊 生成数据库状态报告")
    logger.info('='*50)
    generate_report()
    
    # 总结
    logger.info(f"\n{'='*50}")
    logger.info("📋 检查结果总结")
    logger.info('='*50)
    
    for check_name, result in results.items():
        status = "✅ 通过" if result else "❌ 失败"
        logger.info(f"{check_name}: {status}")
    
    if all_passed:
        logger.info("\n🎉 所有检查都通过！数据库已正确配置，可以正常使用。")
        logger.info("💡 建议:")
        logger.info("   1. 启动应用: python app.py")
        logger.info("   2. 访问初始化接口: curl -X POST http://localhost:5000/api/init-db")
        logger.info("   3. 开始使用系统")
        return 0
    else:
        logger.error("\n❌ 部分检查未通过，请检查错误信息并修复问题。")
        logger.error("💡 可能的解决方案:")
        logger.error("   1. 运行完整初始化脚本: mysql -u root -p news_aggregator < 完整数据库初始化脚本.sql")
        logger.error("   2. 检查数据库配置是否正确")
        logger.error("   3. 确保MySQL服务正在运行")
        return 1

if __name__ == '__main__':
    sys.exit(main())